Create PROCEDURE [dbo].[P_LOCK_WAITING]
	-- Add the parameters for the stored procedure here

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

DECLARE @session_id int
declare @waitSec int
declare cur Cursor for
select session_id,waitingSec from(
select distinct wt.session_id,wait_duration_ms/1000/60 waitingSec,wait_type ,blocking_session_id ,
tl.request_type ,tl.request_status  from sys.dm_os_waiting_tasks wt
left join sys.dm_tran_locks tl
on wt.session_id =tl.request_session_id 
where request_type='LOCK'
) as temp
open cur 
fetch next from cur into @session_id,@waitSec
while @@FETCH_STATUS = 0
begin
--等待时间超过5分钟的就KILL掉
if @waitSec >5
exec('kill '+@session_id)
fetch next from cur into @session_id,@waitSec
end
close cur
DEALLOCATE cur

END

